<?php
 Zend_Loader::loadClass("BaseDBRows");
 Zend_Loader::loadClass("FishingArea");
 class FishingAreas extends BaseDBRows
 {
 	protected function setUp()
 	{
 		$this->_tableName = FishingArea::_tablename;
 	}	
	
	public function getFMAList($country="",$status="",$mac="",$filter="") {
	
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("OrganizationAddress");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("Country");
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("fa.fishing_area_name, fa.area,if(fa.mac_certified,'Yes','No') mac_certified");
		$query->addSelect("concat(a.address1, if(length(a.address2)>0,concat(', ', a.address2),''), if(length(a.city)>0,concat(', ',a.city),'')) address");
		$query->addSelect("a.province, c.country, o.phone, o.fax, o.email, if(o.inactive, 'Yes','No') inactive");
		$query->addFrom($this->_tableName . " fa ");
		$query->addJoin("," . Organization::_tablename . " o ");
		$query->addJoin(" left join " . OrganizationAddress::_tablename . " oa on o.primary_address_id = oa.org_address_id ");
		$query->addJoin(" left join " . Address::_tablename . " a on oa.address_id = a.address_id ");
		$query->addJoin("," . Country::_tablename . " c ");
		$query->addWhere("fa.org_id = o.org_id");
		$query->addWhere("fa.country_code = c.country_code");
		$query->addWhere("o.org_type = 'FMA'");
		$query->addOrderBy("fa.fishing_area_name");
		
		// country
		if(strlen($country) > 0) {
			$query->addWhere("c.country_code = '" . $country . "'");
		}

		// status
		if(strlen($status) > 0) {
			$query->addWhere("o.inactive = " . $status);
		}

		// mac
		if(strlen($mac) > 0) {
			$query->addWhere("fa.mac_certified = " . $mac);
		}

		// filter
		if(strlen($filter) > 0) {
			if ($filter == '#') {
				$query->addWhere("(ascii(lower(left(fa.fishing_area_name,1))) < 97 or ascii(lower(left(fa.fishing_area_name,1))) > 122)");
			} else {
				$query->addWhere("lower(left(fa.fishing_area_name,1)) like '". $filter . "%'");
			}
		}

		$db = DBCon::instance();
		// echo  $query->createSQL();die();
		$result = $db->executeQuery($query->createSQL());
		return $result;
		
	}

 }
?>